from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import json

db = SQLAlchemy()

class User(db.Model):
    __tablename__ = 'users'
    
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    openid = db.Column(db.String(64), nullable=False, unique=True)
    nickname = db.Column(db.String(100))
    avatar_url = db.Column(db.String(255))
    gender = db.Column(db.Integer, default=0)
    country = db.Column(db.String(50))
    province = db.Column(db.String(50))
    city = db.Column(db.String(50))
    language = db.Column(db.String(20))
    create_time = db.Column(db.DateTime, default=datetime.utcnow)
    update_time = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'openid': self.openid,
            'nickname': self.nickname,
            'avatar_url': self.avatar_url,
            'gender': self.gender,
            'country': self.country,
            'province': self.province,
            'city': self.city,
            'language': self.language,
            'create_time': self.create_time.isoformat() if self.create_time else None,
            'update_time': self.update_time.isoformat() if self.update_time else None
        })

class Family(db.Model):
    __tablename__ = 'families'
    
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    family_name = db.Column(db.String(100), nullable=False, default='我的家庭')
    creator_id = db.Column(db.BigInteger, nullable=False)
    create_time = db.Column(db.DateTime, default=datetime.utcnow)
    update_time = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'family_name': self.family_name,
            'creator_id': self.creator_id,
            'create_time': self.create_time.isoformat() if self.create_time else None,
            'update_time': self.update_time.isoformat() if self.update_time else None
        })

class FamilyMember(db.Model):
    __tablename__ = 'family_members'
    
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    family_id = db.Column(db.BigInteger, nullable=False)
    user_id = db.Column(db.BigInteger, nullable=False)
    role = db.Column(db.String(20), nullable=False, default='member')
    join_time = db.Column(db.DateTime, default=datetime.utcnow)
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'family_id': self.family_id,
            'user_id': self.user_id,
            'role': self.role,
            'join_time': self.join_time.isoformat() if self.join_time else None
        })

class DishCategory(db.Model):
    __tablename__ = 'dish_categories'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50), nullable=False)
    description = db.Column(db.String(255))
    sort_order = db.Column(db.Integer, default=0)
    status = db.Column(db.Integer, default=1)
    create_time = db.Column(db.DateTime, default=datetime.utcnow)
    update_time = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'name': self.name,
            'description': self.description,
            'sort_order': self.sort_order,
            'status': self.status,
            'create_time': self.create_time.isoformat() if self.create_time else None,
            'update_time': self.update_time.isoformat() if self.update_time else None
        })

class Dish(db.Model):
    __tablename__ = 'dishes'
    
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    category_id = db.Column(db.Integer, nullable=False)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    price = db.Column(db.Numeric(10, 2), nullable=False, default=0.00)
    image_url = db.Column(db.String(255))
    sales = db.Column(db.Integer, default=0)
    status = db.Column(db.Integer, default=1)
    sort_order = db.Column(db.Integer, default=0)
    create_time = db.Column(db.DateTime, default=datetime.utcnow)
    update_time = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'category_id': self.category_id,
            'name': self.name,
            'description': self.description,
            'price': float(self.price) if self.price is not None else 0.0,
            'image_url': self.image_url,
            'sales': self.sales,
            'status': self.status,
            'sort_order': self.sort_order,
            'create_time': self.create_time.isoformat() if self.create_time else None,
            'update_time': self.update_time.isoformat() if self.update_time else None
        })

class ShoppingCart(db.Model):
    __tablename__ = 'shopping_carts'
    
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    user_id = db.Column(db.BigInteger, nullable=False)
    dish_id = db.Column(db.BigInteger, nullable=False)
    quantity = db.Column(db.Integer, nullable=False, default=1)
    remarks = db.Column(db.String(255))
    meal_type = db.Column(db.String(20), nullable=False)
    create_time = db.Column(db.DateTime, default=datetime.utcnow)
    update_time = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'user_id': self.user_id,
            'dish_id': self.dish_id,
            'quantity': self.quantity,
            'remarks': self.remarks,
            'meal_type': self.meal_type,
            'create_time': self.create_time.isoformat() if self.create_time else None,
            'update_time': self.update_time.isoformat() if self.update_time else None
        })

class Note(db.Model):
    __tablename__ = 'notes'
    
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    user_id = db.Column(db.BigInteger, nullable=False)
    tag = db.Column(db.String(10), nullable=False)
    type = db.Column(db.String(10), nullable=False)
    title = db.Column(db.String(255), nullable=False)
    content = db.Column(db.Text, nullable=False)
    priority = db.Column(db.String(10))
    create_time = db.Column(db.DateTime, default=datetime.utcnow)
    update_time = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'user_id': self.user_id,
            'tag': self.tag,
            'type': self.type,
            'title': self.title,
            'content': self.content,
            'priority': self.priority,
            'create_time': self.create_time.isoformat() if self.create_time else None,
            'update_time': self.update_time.isoformat() if self.update_time else None
        })

class PendingDish(db.Model):
    __tablename__ = 'pending_dishes'
    
    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    family_id   = db.Column(db.BigInteger, nullable=False)
    dish_name   = db.Column(db.String(100), nullable=False)
    quantity    = db.Column(db.Integer, nullable=False, default=1)
    remarks     = db.Column(db.String(255))
    meal_type   = db.Column(db.String(20), nullable=False)
    status      = db.Column(db.Integer, default=0)
    create_time = db.Column(db.DateTime, default=datetime.utcnow)
    update_time = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'family_id': self.family_id,
            'dish_name': self.dish_name,
            'quantity': self.quantity,
            'remarks': self.remarks,
            'meal_type': self.meal_type,
            'status': self.status,
            'create_time': self.create_time.isoformat() if self.create_time else None,
            'update_time': self.update_time.isoformat() if self.update_time else None
        })

class BillCategory(db.Model):
    __tablename__ = 'bill_categories'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50), nullable=False)
    icon = db.Column(db.String(100))
    type = db.Column(db.Integer, nullable=False, default=0)  # 0-支出, 1-收入
    sort_order = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'name': self.name,
            'icon': self.icon,
            'type': self.type,
            'sort_order': self.sort_order,
            'created_at': self.created_at.isoformat() if self.created_at else None
        })

class BillSubCategory(db.Model):
    __tablename__ = 'billsub_categories'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    category_id = db.Column(db.Integer, db.ForeignKey('bill_categories.id'), nullable=False)
    name = db.Column(db.String(50), nullable=False)
    icon = db.Column(db.String(100))
    sort_order = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # 关联关系
    category = db.relationship('BillCategory', backref=db.backref('sub_categories', lazy=True))
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'category_id': self.category_id,
            'name': self.name,
            'icon': self.icon,
            'sort_order': self.sort_order,
            'created_at': self.created_at.isoformat() if self.created_at else None
        })

class Record(db.Model):
    __tablename__ = 'records'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.BigInteger, db.ForeignKey('users.id'), nullable=False)
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    type = db.Column(db.Integer, nullable=False, default=0)  # 0-支出, 1-收入
    category_id = db.Column(db.Integer, db.ForeignKey('bill_categories.id'), nullable=False)
    sub_category_id = db.Column(db.Integer, db.ForeignKey('billsub_categories.id'))
    description = db.Column(db.String(500))
    record_date = db.Column(db.Date, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关联关系
    user = db.relationship('User', backref=db.backref('records', lazy=True))
    category = db.relationship('BillCategory', backref=db.backref('records', lazy=True))
    sub_category = db.relationship('BillSubCategory', backref=db.backref('records', lazy=True))
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'user_id': self.user_id,
            'amount': float(self.amount) if self.amount is not None else 0.0,
            'type': self.type,
            'category_id': self.category_id,
            'sub_category_id': self.sub_category_id,
            'description': self.description,
            'record_date': self.record_date.isoformat() if self.record_date else None,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        })

class ScheduledTask(db.Model):
    __tablename__ = 'scheduled_tasks'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.BigInteger, db.ForeignKey('users.id'), nullable=False)
    email = db.Column(db.String(255), nullable=False)
    frequency = db.Column(db.String(20), nullable=False)  # daily, weekly, monthly
    day_of_week = db.Column(db.Integer)  # 0-6 (Monday-Sunday), only for weekly
    day_of_month = db.Column(db.Integer)  # 1-31, only for monthly
    hour = db.Column(db.Integer, default=9)  # 0-23
    minute = db.Column(db.Integer, default=0)  # 0-59
    is_active = db.Column(db.Boolean, default=True)
    last_run = db.Column(db.DateTime)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关联关系
    user = db.relationship('User', backref=db.backref('scheduled_tasks', lazy=True))
    
    def to_dict(self):
        from utils import safe_json_serialize
        return safe_json_serialize({
            'id': self.id,
            'user_id': self.user_id,
            'email': self.email,
            'frequency': self.frequency,
            'day_of_week': self.day_of_week,
            'day_of_month': self.day_of_month,
            'hour': self.hour,
            'minute': self.minute,
            'is_active': self.is_active,
            'last_run': self.last_run.isoformat() if self.last_run else None,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        })
